## Глава 14. ИСПОЛЬЗОВАНИЕ ПРЕДЛОЖЕНИЯ UNION
 
   В ПРЕДШЕСТВУЮЩИХ ГЛАВАХ, МЫ ОБСУЖДАЛИ различные способы которыми запросы могут помещаться один внутрь другого. Имеется другой способ объединения многочисленых запросов - то-есть формирование их в объединение. В этой главе, вы научитесь использованию предложения UNION в SQL. UNION отличается от подзапросов тем что в нем ни один из двух (или больше ) запросов не управляются другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их - обьединяется.
### ОБЪЕДИНЕНИЕ МНОГОЧИСЛЕННЫХ ЗАПРОСОВ В ОДИН
   Вы можете поместить многочисленые запросы вместе и объединить их вывод используя предложение UNION. Предложение UNION обьединяет вывод двух или более SQL запросов в единый набор строк и столбцов. Например чтобы получить всех продавцов и заказчиков размещенных в Лондоне и вывести их как единое целое вы могли бы ввести:
 
    SELECT snum, sname
    FROM Salespeople
    WHERE city='London'
 
### UNION
 
    SELECT cnum, cname
    FROM Customers
    WHERE city='London';
 
   и получить вывод показанный в Таблице 14.1.
 
   Как вы можете видеть, столбцы выбранные двумя командами выведены так как если она была одна. Заголовки столбца исключены, потому что ни один из столбцов выведенных объединением, не был извлечен непосредственно из только одной таблицы. Следовательно все эти столбцы вывода не имеют никаких имен (смотрите Главу 7 обсуждающую вывод столбцов ).
   Кроме того обратите внимание, что только последний запрос заканчивается точкой с запятой. Отсутствие точки с запятой дает понять SQL, что имеется еще одно или более запросов.
 
SQL Execution Log

    SELECT snum, sname FROM Salespeople WHERE city='London'
    UNION SELECT cnum, cname FROM Customers
    WHERE city='London';
    
|    1001                                  |    Peel                                  |
|------------------------------------------|------------------------------------------|
|    1004<br style="margin: 0px; padding: 0px;"> |    Motika<br style="margin: 0px; padding: 0px;"> |
|    2001<br style="margin: 0px; padding: 0px;"> |    Hoffman<br style="margin: 0px; padding: 0px;"> |
|    2006<br style="margin: 0px; padding: 0px;"> |    Climens<br style="margin: 0px; padding: 0px;"> |
    

   Таблица 14.1: Формирование объединения из двух запросов
### КОГДА ВЫ МОЖЕТЕ ДЕЛАТЬ ОБЪЕДИНЕНИЕ МЕЖДУ ЗАПРОСАМИ?
   Когда два (или более ) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое число столбцов и в том же порядке что и первый, второй, третий, и так далее, и каждый должен иметь тип, совместимый с каждым. Значение совместимости типов - меняется.
   ANSI следит за этим очень строго и поэтому числовые поля должны иметь одинаковый числовой тип и размер, хотя некоторые имена испрользуемые ANSI для этих типов являются - синонимами. (Смотрите Приложение B для подробностей об ANSI числовых типах. ) Кроме того, символьные поля должны иметь одинаковое число символов (значение предначначенного номера, не обязательно такое же как используемый номер). Хорошо, что некоторые SQL программы обладают большей гибкостью чем это определяется ANSI. Типы не определенные ANSI, такие как DATA и BINARY, обычнодолжны совпадать с другими столбцами такого же нестандартного типа.
   Длина строки также может стать проблемой. Большинство программ разрешают поля переменной длины, но они не обязательно будут использоваться с UNION. С другой стороны, некоторые программы (и ANSI тоже) требуют чтобы символьные поля были точно равной длины. В этих вопросах вы должны проконсультироваться с документацией вашей собственной программы.
   Другое ограничение на совместимость - это когда пустые значения(NULL) запрещены в любом столбце объединения, причем эти значения необходимо запретить и для всех соответствующих столбцов в других запросах объединения. Пустые значения(NULL) запрещены с ограничением NOT NULL, которое будет обсуждаться в Главе 18. Кроме того, вы не можете использовать UNION в подзапросах, а также не можете использовать агрегатные функции в предложениии SELECT запроса в объединении. (Большинство программ пренебрегают этими ограничениями. )
### UNION И УСТРАНЕНИЕ ДУБЛИКАТОВ
   UNION будет автоматически исключать дубликаты строк из вывода. Это нечто несвойственное для SQL, так как одиночные запросы обычно содержат DISTINCT чтобы устранять дубликаты. Например запрос, чей вывод показывается в Таблице 14.2,
 
    SELECT snum, city
    FROM Customers;
 
   имеет двойную комбинацию значений (snum=1001, city=London ), потомучто мы не указали, чтобы SQL устранил дубликаты. Однако, если мы используем
 
SQL Execution Log

    SELECT snum, city FROM Customers;

|    snum                                  |    city                                  |
|------------------------------------------|------------------------------------------|
|    1001<br style="margin: 0px; padding: 0px;"> |    London<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    Rome<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    San Jose<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    Berlin<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    London<br style="margin: 0px; padding: 0px;"> |
|    1004<br style="margin: 0px; padding: 0px;"> |    Rome<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    San Jose<br style="margin: 0px; padding: 0px;"> |


   Таблица 14.2: Одиночный запрос с дублированным выводом
   UNION в комбинации этого запроса с ему подобным в таблице Продавцов, то эта избыточная комбинация будет устранена. Таблица 14.3 показывает вывод следующего запроса.
 
    SELECT snum, city
    FROM Customers

    UNION
 
    SELECT snum, city
    FROM Salespeople.;
 
SQL Execution Log

    FROM Customers UNION SELECT snum, sity
    FROM Salespeople;

|    1001                                  |    London                                |
|------------------------------------------|------------------------------------------|
|    1002<br style="margin: 0px; padding: 0px;"> |    Berlin<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    San Jose<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    New York<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    Rome<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    London<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    Rome<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    Barcelona<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    San Jose<br style="margin: 0px; padding: 0px;"> |


   Таблица 14.3: UNION устраняет двойной вывод
 
   Вы можете получить нечто похожее (в некоторых программах SQL, используя UNION ALL вместо просто UNION, наподобии этого:
 
    SELECT snum, city
    FROM Customers
 
    UNION ALL
 
    SELECT snum, city
    FROM Salespeople;
 
### ИСПОЛЬЗОВАНИЕ СТРОК И ВЫРАЖЕНИЙ С UNION
   Иногда, вы можете вставлять константы и выражения в предложения SELECT используемые с UNION. Это не следует строго указаниям ANSI, но это полезная и необычно используемая возможность. Константы и выражения которые вы используете, должны встречать совместимые стандарты которые мы выделяли ранее. Эта свойство полезно, например, чтобы устанавливать комментарии указывающие какой запрос вывел данную строку.
   Предположим что вы должны сделать отчет о том, какие продавцы производят наибольшие и наименьшие порядки по датам. Мы можем объединить два запроса, вставив туда текст чтобы различать вывод для каждого из них.
 
    SELECT a.snum, sname, onum, 'Highest on', odate
    FROM (Salespeople a, Orders b
    WHERE a.snum=b.snum
    AND b.amt=
    ( SELECT MAX (amt)
    FROM Orders c
    WHERE c.odate=b.odate )
    
    UNION
    
    SELECT a.snum, (sname, (onum ' Lowest on', odate
    FROM (Salespeople a, Orders b
    WHERE a.snum=b.snum
    AND b.amt=
    ( SELECT MIN (amt)
    FROM Orders c
    WHERE c.odate=b.odate );

   Вывод из этой команды показывается в Таблице 14.4.
 
   Мы должны были добавить дополнительный пробел в строку 'Lowest on', чтобы сделать ее совпадающей по длине со строкой 'Highest on'. Обратите внимание что Peel выбран при наличии и самого высокого и самого низкого (фактически он единственый ) порядка на 5 Октября. Так как вставляемые строки двух этих запросов различны, строки не будут устранены как дубликаты.
 
SQL Execution Log
 
    AND b.amt=( SELECT min (amt) FROM Orders c
    WHERE c.odate=b.odate)
    
|    1001                                  |    Peel                                  |    3008                                  |    Highest on                            |    10/05/1990                            |
|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    3008<br style="margin: 0px; padding: 0px;"> |    Lowest on<br style="margin: 0px; padding: 0px;"> |    10/05/1990<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    3011<br style="margin: 0px; padding: 0px;"> |    Highest on<br style="margin: 0px; padding: 0px;"> |    10/06/1990<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    Serres<br style="margin: 0px; padding: 0px;"> |    3005<br style="margin: 0px; padding: 0px;"> |    Highest on<br style="margin: 0px; padding: 0px;"> |    10/03/1990<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    Serres<br style="margin: 0px; padding: 0px;"> |    3007<br style="margin: 0px; padding: 0px;"> |    Lowest on<br style="margin: 0px; padding: 0px;"> |    10/04/1990<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    Serres<br style="margin: 0px; padding: 0px;"> |    3010<br style="margin: 0px; padding: 0px;"> |    Lowest on<br style="margin: 0px; padding: 0px;"> |    10/06/1990<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    Axelrod<br style="margin: 0px; padding: 0px;"> |    3009<br style="margin: 0px; padding: 0px;"> |    Highest on<br style="margin: 0px; padding: 0px;"> |    10/04/1990<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    Rifkin<br style="margin: 0px; padding: 0px;"> |    3001<br style="margin: 0px; padding: 0px;"> |    Lowest on<br style="margin: 0px; padding: 0px;"> |    10/03/1990<br style="margin: 0px; padding: 0px;"> |

 Таблица 14.4: Выбор наивысших и наинизших порядков, определяемых с помощью строк
 
### ИСПОЛЬЗОВАНИЕ UNION С ORDER BY
   До сих пор, мы не оговаривали что данные многочисленых запросов будут выводиться в каком то особом порядке. Мы просто показывали вывод сначала из одного запроса а затем из другого. Конечно, вы не можете полагаться на вывод приходящий в произвольном порядке. Мы как раз сделаем так чтобы этот способ для выполнения примеров был более простым.
   Вы можете, использовать предложение ORDER BY чтобы упорядочить вывод из объединения, точно так же как это делается в индивидуальных запросах. Давайте пересмотрим наш последний пример чтобы упорядочить имена с помощью их порядковых номеров. Это может внести противоречие, такое как повторение имени Peel в последней команде, как вы сможете увидеть из вывода показанного в Таблице 14.5.
 
    SELECT a.snum, sname, onum, 'Highest on', odate
    FROM Salespeople a, Orders b
    WHERE a.snum=b.snum
    AND b.amt=
    ( SELECT MAX (amt)
    FROM Orders c
    WHERE c.odate=b.odate )
    
    UNION
    SELECT a.snum, (sname, (onum, 'Lowest on', odat
    FROM Salespeople a, Orders b
    WHEREa.snum=b.snum
     AND b.amt=
    ( SELECT MIN (amt)
    FROM Orders c
    WHERE c.odate=b.odate )
 
    ORDER BY 3;
 
SQL Execution Log
 
    SELECT min (amt) FROM Orders c
    WHERE c.odate=b.odate) ORDER BY 3;
    
|    1007                                  |    Rifkin                                |    3001                                  |    Lowest on                             |    10/03/1990                            |
|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|
|    1002<br style="margin: 0px; padding: 0px;"> |    Serres<br style="margin: 0px; padding: 0px;"> |    3005<br style="margin: 0px; padding: 0px;"> |    Highest on<br style="margin: 0px; padding: 0px;"> |    10/03/1990<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    Serres<br style="margin: 0px; padding: 0px;"> |    3007<br style="margin: 0px; padding: 0px;"> |    Lowest on<br style="margin: 0px; padding: 0px;"> |    10/04/1990<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    3008<br style="margin: 0px; padding: 0px;"> |    Highest on<br style="margin: 0px; padding: 0px;"> |    10/05/1990<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    3008<br style="margin: 0px; padding: 0px;"> |    Lowest on<br style="margin: 0px; padding: 0px;"> |    10/05/1990<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    Axelrod<br style="margin: 0px; padding: 0px;"> |    3009<br style="margin: 0px; padding: 0px;"> |    Highest on<br style="margin: 0px; padding: 0px;"> |    10/04/1990<br style="margin: 0px; padding: 0px;"> |
|    1002<br style="margin: 0px; padding: 0px;"> |    Serres<br style="margin: 0px; padding: 0px;"> |    3010<br style="margin: 0px; padding: 0px;"> |    Lowest on<br style="margin: 0px; padding: 0px;"> |    10/06/1990<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    3011<br style="margin: 0px; padding: 0px;"> |    Highest on<br style="margin: 0px; padding: 0px;"> |    10/06/1990<br style="margin: 0px; padding: 0px;"> |

Таблица 14.5: Формирование объединения с использованием ORDER BY
   Пока ORDER BY используется по умолчанию, мы не должны его указывать.
   Мы можем упорядочить наш вывод с помощью нескольких полей, одно внутри другого и указать ASC или DESC для каждого, точно также как мы делали это для одиночных запросов. Заметьте, что номер 3 в предложении ORDER BY указывает какой столбец из предложения SELECT будет упорядочен. Так как столбцы объединения - это столбцы вывода, они не имеют имен, и следовательно, должны определяться по номеру. Этот номер указывает на их место среди других столбцов вывода. (Смотрите Главу 7, обсуждающую столбцы вывода.)
### ВНЕШНЕЕ ОБЪЕДИНЕНИЕ
   Операция которая бывает часто полезна - это объединение из двух запросов в котором второй запрос выбирает строки, исключенные первым. Наиболее часто, вы будете делать это, так чтобы не исключать строки которые не удовлетворили предикату при объединении таблиц. Это называется - внешним обьединением. Предположим что некоторые из ваших заказчиков еще не были назначены к продавцам. Вы можете захотеть увидеть имена и города всех ваших заказчиков, с именами их продавцов, не учитывая тех кто еще не был назначен. Вы можете достичь этого, формируя объединение из двух запросов, один из которых выполняет обьединение, а другой выбирает заказчиков с пустыми(NULL) значениями поля snum. Этот последний запрос должен вставлять пробелы в поля соответствующие полю sname в первом запросе. Как и раньше, вы можете вставлять текстовые строки в ваш вывод чтобы идентифицировать запрос который вывел данную строку. Использование этой методики во внешнем обьединении, дает возможность использовать предикаты для классификации, а не для исключения.
   Мы использовали пример нахождения продавцов с заказчиками размещенными в их городах и раньше. Однако вместо просто выбора только этих строк, вы возможно захотите чтобы ваш вывод перечислял всех продавцов, и указывал тех, кто не имел заказчиков в их городах, и кто имел. Следующий запрос, чей вывод показывается в Таблице 14.6, выполнит это:
 
    SELECT Salespeople.snum, sname, cname, comm
    FROM (Salespeople, Customers
    WHERE Salespeople.city=Customers.city.
    
    UNION
    
    SELECT snum, sname, ' NO MATCH ', comm
    FROM (Salespeople
    WHERE NOT city=ANY
    ( SELECT city
    FROM Customers )
    
    ORDER BY 2 DESC;
 
SQL Execution Log
 
    FROM Salespeople WHERE NOT city=ANYate)
    ( SELECT city FROM Customers) ORDER BY 2 DESC;
    
|    1002                                  |    Serres                                |    Cisneros                              |    0.1300                                |
|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|
|    1002<br style="margin: 0px; padding: 0px;"> |    Serres<br style="margin: 0px; padding: 0px;"> |    Liu<br style="margin: 0px; padding: 0px;"> |    0.1300<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    Rifkin<br style="margin: 0px; padding: 0px;"> |    NO MATCH<br style="margin: 0px; padding: 0px;"> |    0.1500<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    Clemens<br style="margin: 0px; padding: 0px;"> |    0.1200<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    Hoffman<br style="margin: 0px; padding: 0px;"> |    0.1200<br style="margin: 0px; padding: 0px;"> |
|    1004<br style="margin: 0px; padding: 0px;"> |    Motika<br style="margin: 0px; padding: 0px;"> |    Clemens<br style="margin: 0px; padding: 0px;"> |    0.1100<br style="margin: 0px; padding: 0px;"> |
|    1004<br style="margin: 0px; padding: 0px;"> |    Motika<br style="margin: 0px; padding: 0px;"> |    Hoffman<br style="margin: 0px; padding: 0px;"> |    0.1100<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    Axelrod<br style="margin: 0px; padding: 0px;"> |    NO MATCH<br style="margin: 0px; padding: 0px;"> |    0.1000<br style="margin: 0px; padding: 0px;"> |


   Таблица 14. 6: Внешнее обьединение
 
   Строка 'NO MATCH' была дополнена пробелами, чтобы получить совпадение поля cname по длине (это не обязательно во всех реализациях SQL). Второй запрос выбирает даже те строки которые исключил первый. Вы можете также добавить комментарий или выражение к вашему запросу, ввиде дополнительного поля. Если вы сделаете это, вы будете должны добавить некоторый дополнительный комментарий или выражение, в той же самой позиции среди выбранных полей, для каждого запроса в операции объединения. Совместимость UNION предотвращает вас от добавления дополнительного поля для первого запроса, но не для второго. Имеется запрос который добавляет строки к выбранным полям, и указывает совпадает ли данный продавец с его заказчиком в его городе:
 
    SELECT a.snum, sname, a.city, ' MATCHED '
    FROM Salespeople a, Customers b
    WHERE a.city=b.city
    
    UNION
    
    SELECT snum, sname, city, 'NO MATCH'
    FROM Salespeople
    WHERE NOT city=ANY
    ( SELECT city
    FROM Customers )
    
    ORDER BY 2 DESC;
 
   Таблица 14,7 показывает вывод этого запроса.
 
SQL Execution Log
 
    WHERE a.city=b.city UNION SELECT snum,sname,city, 'NO MATCH'
    FROM Salespeople WHERE NOT city=ANYate)
    ( SELECT city FROM Customers) ORDER BY 2 DESC;
    
|    1002                                  |    Serres                                |    San Jose                              |    MATCHED                               |
|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|
|    1007<br style="margin: 0px; padding: 0px;"> |    Rifkin<br style="margin: 0px; padding: 0px;"> |    Barselona<br style="margin: 0px; padding: 0px;"> |    NO MATCH<br style="margin: 0px; padding: 0px;"> |
|    1001<br style="margin: 0px; padding: 0px;"> |    Peel<br style="margin: 0px; padding: 0px;"> |    London<br style="margin: 0px; padding: 0px;"> |    MATCHED<br style="margin: 0px; padding: 0px;"> |
|    1004<br style="margin: 0px; padding: 0px;"> |    Motika<br style="margin: 0px; padding: 0px;"> |    London<br style="margin: 0px; padding: 0px;"> |    MATCHED<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    Axelrod<br style="margin: 0px; padding: 0px;"> |    New York<br style="margin: 0px; padding: 0px;"> |    NO MATCH<br style="margin: 0px; padding: 0px;"> |

Таблица 14. 7: Внешнее обьединение с полем коментария
   Это не полное внешнее объединение, так как оно включает только несовпадающие поля одной из объединяемых таблиц. Полное внешнеее объединение должно включать всех заказчиков имеющих и не имеющих продавцов в их городах. Такое условие будет более полным, как вы это сможете увидеть (вывод следующего запроса показан в Таблице 14,8) :
 
    SELECT snum, city, 'SALESPERSON - MATCH'
    FROM Salespeople
    WHERE NOT city=ANY
    (SELECT city
    FROM Customers)
    
    UNION
    
    SELECT snum, city, 'SALESPERSON - NO MATCH'
    FROM Salespeople
    WHERE NOT city=ANY
    (SELECT city
    FROM Customers))
    
    UNION
    
    (SELECT cnum, city, 'CUSTOMER - MATCHED'
    FROM Customers
    WHERE city=ANY
    (SELECT city
    FROM Salespeople)
    
    UNION
    
    SELECT cnum, city, 'CUSTOMER - NO MATCH'
    FROM Customers
    WHERE NOT city=ANY
    (SELECT city
    FROM Salespeople))
    
    ORDER BY 2 DESC;
 
 
SQL Execution Log

    FROM Salespeople) ORDER BY 2 DESC;
    
|    2003                                  |    San Jose                              |    CUSTOMER - MATCHED                    |
|------------------------------------------|------------------------------------------|------------------------------------------|
|    2008<br style="margin: 0px; padding: 0px;"> |    San Jose<br style="margin: 0px; padding: 0px;"> |    CUSTOMER - MATCHED<br style="margin: 0px; padding: 0px;"> |
|    2002<br style="margin: 0px; padding: 0px;"> |    Rome<br style="margin: 0px; padding: 0px;"> |    CUSTOMER - NO MATCH<br style="margin: 0px; padding: 0px;"> |
|    2007<br style="margin: 0px; padding: 0px;"> |    Rome<br style="margin: 0px; padding: 0px;"> |    CUSTOMER - NO MATCH<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    New York<br style="margin: 0px; padding: 0px;"> |    SALESPERSON - MATCHED<br style="margin: 0px; padding: 0px;"> |
|    1003<br style="margin: 0px; padding: 0px;"> |    New York<br style="margin: 0px; padding: 0px;"> |    SALESPERSON - NO MATCH<br style="margin: 0px; padding: 0px;"> |
|    2001<br style="margin: 0px; padding: 0px;"> |    London<br style="margin: 0px; padding: 0px;"> |    CUSTOMER - MATCHED<br style="margin: 0px; padding: 0px;"> |
|    2006<br style="margin: 0px; padding: 0px;"> |    London<br style="margin: 0px; padding: 0px;"> |    CUSTOMER - MATCHED<br style="margin: 0px; padding: 0px;"> |
|    2004<br style="margin: 0px; padding: 0px;"> |    Berlin<br style="margin: 0px; padding: 0px;"> |    CUSTOMER - NO MATCH<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    Barcelona<br style="margin: 0px; padding: 0px;"> |    SALESPERSON - MATCHED<br style="margin: 0px; padding: 0px;"> |
|    1007<br style="margin: 0px; padding: 0px;"> |    Barcelona<br style="margin: 0px; padding: 0px;"> |    SALESPERSON - NO MATCH<br style="margin: 0px; padding: 0px;"> |

Таблица 1.8: Полное внешнее обьединение
   ( Понятно, что эта формула использующая ANY - эквивалентна обьединению в предыдущем примере. )
   Сокращенное внешнее обьединение с которого мы начинали, используется чаще чем этот последний пример. Этот пример, однако, имеет другой смысл. Всякий раз, когда вы выполняете объединение более чем двух запросов, вы можете использовать круглые скобки чтобы определить порядок оценки. Другими словами, вместо просто -
 
    query X UNION query Y UNION query Z;
 
   вы должны указать, или
 
    ( query X UNION query Y )UNION query Z;
 
   или
    query X UNION (query Y UNION query Z );
 
   Это потому, что UNION и UNION ALL могут быть скомбинированны, чтобы удалять одни дубликаты, не удаляя других. Предложение -
 
    ( query X UNION ALL query Y )UNION query Z;
 
   не обязательно воспроизведет те же результаты что предложение -
 
    query X UNION ALL( query Y UNION query Z );
 
   если двойные строки в нем, будут удалены.
 
### РЕЗЮМЕ
   Теперь вы знаете как использовать предложение UNION, которое дает возможность объединять любое число запросов в единое тело вывода. Если вы имеете ряд подобных таблиц - таблиц, содержащих похожую информацию, но принадлежащую разным пользователям и охватывающую различные особенности, возможно - что объединение сможет обеспечить простой способ для слияния и упорядочивания вывода. Аналогично, внешние обьединения дают вам новый способ использования условий, не для исключения вывода, а для его маркировки или обработки его частей, когда встречается условие отличающееся от того, которое не выполняется.
   Этим заканчиваются наши главы о запросах. Вы теперь имеете довольно полное представление о поиске данных в SQL. Следующий шаг должен включать то, как значения вводятся в таблицы и как таблицы создаются с самого начала. Как вы увидете, запросы иногда используются внутри других типов команд, также хорошо как и сами по себе.
### РАБОТА С SQL
   * Создайте объединение из двух запросов которое показало бы имена, города, и оценки всех заказчиков. Те из них которые имеют поле rating=200 и более, должны кроме того иметь слова - "Высокий Рейтинг", а остальные должны иметь слова " Низкий Рейтинг ".
   * Напишите команду которая бы вывела имена и номера каждого продавц и каждого заказчика которые имеют больше чем один текущий порядок. Результат представьте в алфавитном порядке.
   * Сформируйте объединение из трех запросов. Первый выбирает поля snum всех продавцов в San Jose; второй, поля cnum всех заказчиков в San Jose; и третий поля onum всех порядков на 3 Октября. Сохраните дубликаты между последними двумя запросами, но устраните любую избыточность вывода между каждым из их и самым первым. (Примечание: в данных типовых таблицах, не содержится никакой избыточности. Это только пример.)

## Глава 15. ВВОД, УДАЛЕНИЕ и ИЗМЕНЕНИЕ ЗНАЧЕНИЙ ПОЛЕЙ
 
   ЭТА ГЛАВА ПРЕДСТАВЛЯЕТ КОМАНДЫ КОТОРЫЕ управляют значениями представляемыми в таблице. Когда вы закончите эту главу, вы будете способны помещать строки в таблицу, удалять их, и изменять индивидуальные значения представленные в каждой строке.
 
   Будет показано использование запросов в формировании полной группы строк для вставки, а также, как может использоваться предикат для управления изменения значений и удаления строк. Материал в этой главе составляет полный объем знаний показывающий, как создавать и управлять информацией в базе данных.
   Более мощные способы проектирования предикатов будут обсуждены в следующей главе.
### КОМАНДЫ МОДИФИКАЦИИ ЯЗЫКА DML
   Значения могут быть помещены и удалены из полей, тремя командами языка DML (Язык Манипулирования Данными ):
 
    INSERT (ВСТАВИТЬ),
    UPDATE (МОДИФИЦИРОВАТЬ),
    DELETE (УДАЛИТЬ).
 
   Не смущайтесь, все они упоминались ранее в SQL, как команды модификации.
   ВВОД ЗНАЧЕНИЙ
   Все строки в SQL вводятся с использованием команды модификации INSERT. В самой простой форме, INSERT использует следующий синтаксис:
 
    INSERT INTO <table name>
    VALUES (<value>, <value> . . .);
 
   Так, например, чтобы ввести строку в таблицу Продавцов, вы можете использовать следующее условие:
 
    INSERT INTO Salespeople
    VALUES (1001, 'Peel', 'London', .12);
 
   Команды DML не производят никакого вывода, но ваша программа должна дать вам некоторое подтверждение того что данные были использованы.
   Имя таблицы (в нашем случае - Salespeople (Продавцы)), должно быть предварительно определено, в команде CREATE TABLE (см. Главу 17 ), а каждое значение пронумерованное в предложении значений, должно совпадать с типом данных столбца, в который оно вставляется. В ANSI, эти значения не могут составлять выражений, что означает что 3 - это доступно, а выражение 2 + 1 - нет. Значения, конечно же, вводятся в таблицу в поименном порядке, поэтому первое значение с именем, автоматическми попадает в столбец 1, второе в столбец 2, на так далее.

### ВСТАВКА ПУСТЫХ УКАЗАТЕЛЕЙ (NULL)
   Если вам нужно ввести пустое значение(NULL), вы вводите его точно также как и обычное значение. Предположим, что еще не имелось поля city для мистера Peel. Вы можете вставить его строку со значением=NULL в это поле, следующим образом:
 
    INSERT INTO Salespeople
    VALUES (1001, 'Peel', NULL, .12);
 
   Так как значение NULL - это специальный маркер, а не просто символьное значение, он не включяется в одиночные кавычки.
   ИМЕНОВАНИЕ СТОЛБЦА ДЛЯ ВСТАВКИ (INSERT)
   Вы можете также указывать столбцы, куда вы хотите вставить значение имени. Это позволяет вам вставлять имена в любом порядке. Предположим что вы берете значения для таблицы Заказчиков из отчета выводимого на принтер, который помещает их в таком порядке: city, cname, и cnum, и для упрощения, вы хотите ввести значения в том же порядке:
 
    INSERT INTO Customers (city, cnamе, cnum)
    VALUES ('London', 'Honman', 2001);
 
   Обратите внимание что столбцы rating и snum - отсутствуют. Это значит, что эти строки автоматически установлены в значение - по умолчанию. По умолчанию может быть введено или значение NULL или другое значение определяемое как - по умолчанию. Если ограничение запрещает использование значения NULL в данном столбце, и этот столбец не установлен как по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT которая относится к таблице( смотри Главу 18 для информации об ограничениях на NULL и на "по умолчанию" ).
### ВСТАВКА РЕЗУЛЬТАТОВ ЗАПРОСА
   Вы можете также использовать команду INSERT чтобы получать или выбирать значения из одной таблицы и помещать их в другую, чтобы использовать их вместе с запросом. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос:
 
    INSERT INTO Londonstaff
    SELECT *
    FROM Salespeople
    WHERE city='London';
   Здесь выбираются все значения произведенные запросом - то-есть все строки из таблицы Продавцов со значениями city="London" - и помещаются в таблицу называемую Londonstaff. Чтобы это работало, таблица Londonstaff должна отвечать следующим условиям:
   * Она должна уже быть создана командой CREATE TABLE.
   * Она должна иметь четыре столбца которые совпадают с таблицей Продавцов в терминах типа данных; то-есть первый, второй, и так далее, столбцы каждой таблицы, должны иметь одинаковый тип данных (причем они не должны иметь одинаковых имен ).
 
   Общее правило то, что всталяемые столбцы таблицы, должны совпадать со столбцами выводимыми подзапросом, в данном случае, для всей таблицы Продавцов.
   Londonstaff - это теперь независимая таблица которая получила некоторые значения из таблицы Продавцов(Salespeople). Если значения в таблице Продавцов будут вдруг изменены, это никак не отразится на таблицеLondonstaff (хотя вы могли бы создать такой эффект, с помощью Представления( VIEW), описанного в Главе 20 ).
   Так как или запрос или команда INSERT могут указывать столбцы по имени, вы можете, если захотите, переместить только выбранные столбцы а также переупорядочить только те столбцы которые вы выбрали. Предположим, например, что вы решили сформировать новую таблицу с именем Daytotals, которая просто будет следить за общим количеством долларов сумм приобретений упорядоченных на каждый день. Вы можете ввести эти данные независимо от таблицы Порядков, но сначала вы должны заполнить таблицу Daytotals информацией ранее представленной в таблице Порядков.
   Понимая что таблица Поряжков охватывает последний финансовый год, а не только несколько дней, как в нашем примере, вы можете видеть преимущество использования следующего условия INSERT в подсчете и вводе значений
 
    INSERT INTO Daytotals (date, total)
    SELECT odate, SUM (amt)
    FROM Orders
    GROUP BY odate;
 
   Обратите внимание что, как предложено ранее, имена столбцов таблицы Порядков и таблицы Daytotals - не должны быть одинаковыми. Кроме того, если дата приобретения и общее количество - это единственые столбцы в таблице, и они находятся в данном порядке, их имена могут быть исключены из вывода из-за их очевидной простоты.
###\УДАЛЕНИЕ СТРОК ИЗ ТАБЛИЦ
   Вы можете удалять строки из таблицы командой модификации - DELETE.
   Она может удалять только введеные строки, а не индивидуальные значения полей, так что параметр поля является необязательным или недоступным.
   Чтобы удалить все содержание таблицы Продавцов, вы можете ввести следующее условие:
 
### DELETE FROM Salespeople;
 
   Теперь когда таблица пуста ее можно окончательно удалить командой DROP TABLE (это объясняется в Главе 17 ).
   Обычно, вам нужно удалить только некоторые определенные строки из таблицы. Чтобы определить какие строки будут удалены, вы используете предикат, так же как вы это делали для запросов. Например, чтобы удалить продавца Axelrod из таблицы, вы можете ввести
 
    DELETE FROM Salespeople
    WHERE snum=1003;
 
   Мы использовали поле snum вместо поля sname потому, что это лучшая тактика при использовании первичных ключей когда вы хотите чтобы действию подвергалась одна и только одна строка. Для вас - это аналогично действию первичного ключя.
   Конечно, вы можете также использовать DELETE с предикатом который бы выбирал группу строк, как показано в этом примере:
 
    DELETE FROM Salespeople
    WHERE city='London';
 
### ИЗМЕНЕНИЕ ЗНАЧЕНИЙ ПОЛЯ
   Теперь, когда вы уже можете вводить и удалять строки таблицы, вы должны узнать как изменять некоторые или все значения в существующей строке.
   Это выполняется командой UPDATE. Эта команда содержит предложение UPDATE в которой указано имя используемой таблицы и предложение SET которое указывает на изменение которое нужно сделать для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, вы можете ввести
 
    UPDATE Customers
    SET rating=200;
### МОДИФИЦИРОВАНИЕ ТОЛЬКО ОПРЕДЕЛЕННЫХ СТРОК
   Конечно, вы не всегда захотите указывать все строки таблицы для изменения единственного значения, так что UPDATE, наподобии DELETE, может брать предикаты. Вот как например можно выполнить изменение одинаковое для всех заказчиков продавца Peel (имеющего snum=1001 ):
 
    UPDATE Customers
    SET rating=200
    WHERE snum=1001;
### КОМАНДА UPDATE ДЛЯ МНОГИХ СТОЛБЦОВ
   Однако, вы не должны, ограничивать себя модифицированием единственного столбца с помощью команды UPDATE. Предложение SET может назначать любое число столбцов, отделяемых запятыми. Все указанные назначения могут быть сделаны для любой табличной строки, но только для одной в каждый момент времени. Предположим, что продавец Motika ушел на пенсию, и мы хотим переназначить его номер новому продавцу:
 
    UPDATE Salespeople
    SET sname='Gibson',city='Boston',comm=.10
    WHERE snum=1004;
 
   Эта команда передаст новому продавцу Gibson, всех текущих заказчиков бывшего продавца Motika и порядки, в том виде в котором они были скомпонованы для Motika с помощью поля snum. Вы не можете, однако, модифицировать сразу много таблиц в одной команде, частично потому, что вы не можете использовать префиксы таблицы со столбцами измененными предложением SET. Другими словами, вы не можете сказать - "SET Salespeople.sname=Gibson" в команде UPDATE, вы можете сказать только так - "SET sname =Gibson".
### ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ ДЛЯ МОДИФИКАЦИИ
   Вы можете использовать скалярные выражения в предложении SET команды UPDATE, однако, включив его в выражение поля которое будет изменено. В этом их отличие от предложения VALUES команды INSERT, в котором выражения не могут использоваться; это свойство скалярных выражений весьма полезная особенность. Предположим, что вы решили удвоить комиссионные всем вашим продавцам. Вы можете использовать следующее выражение:
 
    UPDATE Salespeople
    SET comm=comm * 2;
 
   Всякий раз, когда вы ссылаетесь к указанному значению столбца в предложении SET, произведенное значение может получится из текущей строки, прежде в ней будут сделаны какие-то изменения с помощью команды UPDATE. Естественно, вы можете скомбинировать эти особенности, и сказать, - удвоить комиссию всем продавцам в Лондоне, таким предложением:
 
    UPDATE Salespeople
    SET comm=comm * 2
    WHERE city='London';
### МОДИФИЦИРОВАНИЕ ПУСТЫХ(NULL) ЗНАЧЕНИЙ
   Предложение SET - это не предикат. Он может вводить пустые NULL значения также как он вводил значения не используя какого-то специального синтаксиса (такого например как IS NULL). Так что, если вы хотите установить все оценки заказчиков в Лондоне в NULL, вы можете ввести следующее предложение:
 
    UPDATE customers
    SET rating=NULL
    WHERE city='London';
 
   что обнулит все оценки заказчиков в Лондоне.
### РЕЗЮМЕ
   Теперь вы овладели мастерством управления содержанием вашей базы данных с помощью трех простых команд:
 
   **INSERT** - используемой чтобы помещать строки в базу данных;
   **DELETE** - чтобы удалять их;
   **REFERENCES** - чтобы изменять значения в уже вставленных строках.
   Вы обучались использованию предиката с командами UPDATE и DELETE чтобы определять, на которую из строк будет воздействовать команда.
   Конечно, предикаты как таковые - не значимы для INSERT, потому что обсуждаемая строка не существует в таблице до окончания выполнения команды INSERT. Однако, вы можете использовать запросы с INSERT, чтобы сразу помещать все наборы строк в таблицу. Причем это, вы можете делать со столбцами в любом порядке.
   Вы узнали, что значения по умолчанию, могут помещаться в столбцы,если вы не устанавливаете это значение явно. Вы также видели использование стандартного значения по умолчанию, которым является NULL. Кроме того, вы поняли, что UPDATE может использовать выражение значения, тогда как INSERT не может.
   Следующая глава расширит ваше познания, показав вам, как использовать подзапросы с этими командами. Эти подзапросы напоминают те, с которыми вы уже знакомы, но имеются некоторые специальные выводы и ограничения, когда подзапросы используются в командах DML, что мы будем обсуждать в Главе 16.
 
### РАБОТА С SQL
 
   Напишите команду которая бы поместила следующие значения, в их нижеуказанном порядке, в таблицу Продавцов:
   city - San Jose,
   name - Bianco,
   comm - NULL,
   * cnum - 1100.
   * Напишите команду которая бы удалила все порядки заказчика Clemens из таблицы Порядков.
   * Напишите команду которая бы увеличила оценку всех заказчиков в Риме на 100.
   * Продавец Serres оставил компанию. Переназначьте его заказчиков продавцу Motika.

## Глава 16. ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С КОМАНДАМИ МОДИФИКАЦИИ
 
   В ЭТОЙ ГЛАВЕ, ВЫ УЗНАЕТЕ КАК ИСПОЛЬЗОВАТЬ подзапросы в командах модификации. Вы найдете, что нечто подобное - вы уже видели при использовании подзапросов в запросах. Понимание, как подзапросы используются в командах SELECT, cделает их применение в командах модификации более уверенным, хотя и останутся некоторые вопросы.
 
   Завершением команды SELECT является подзапрос, но не предикат, и поэтому его использование отличается от использования простых предикатов с командами модификации, которые вы уже выполняли ранеее с командами UPDATE и DELETE. Вы использовали простые запросы чтобы производить значения для INSERT, а теперь мы можем расширить эти запросы чтобы включять в них подзапросы.
   Важный принцип который надо соблюдать при работе с командами модификации, состоит в том, что вы не можете в предложении FROM любого подзапроса, модифицировать таблицу к которой ссылаетесь с помощью основной команды. Это относится ко всем трем командам модификации. Хотя имеется большое количество ситуаций в которых будет полезно сделать запрос той таблицы которую вы хотите модифицировать причем во время ее модификации, это слишком усложняет операцию чтобы использовать ее на практике.

Не делайте ссылки к текущей строке таблицы указанной в команде, которая является соотнесенным подзапросом.
### ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С INSERT
   INSERT - это самый простой случай. Вы уже видели как вставлять результаты запроса в таблицу. Вы можете использовать подзапросы внутри любого запроса, который генерирует значения для команды INSERT тем же самым способом, которым вы делали это для других запросов - т.е. внутри предиката или предложения HAVING.
   Предположим, что мы имеем таблицу с именем SJpeople, столбцы которой совпадают со столбцами нашей таблицы Продавцов. Вы уже видели как заполнять таблицу подобно этой, заказчиками в городе, например, в San Jose:
 
    INSERT INTO SJpeople
    SELECT *
    FROM Salespeople
    WHERE city='San Jose';
 
   Теперь мы можем использовать подзапрос чтобы добавить к таблице SJpeople всех продавцов которые имеют заказчиков в San Jose, независимо от того, находятся ли там продавцы или нет:
 
    INSERT INTO SJpeople
    SELECT *
    FROM Salespeople
    WHERE snum=ANY
    ( SELECT snum
    FROM Customers
    WHERE city=' (San (Jose' );
 
   Оба запроса в этой команде функционируют также как если бы они не являлись частью выражения INSERT. Подзапрос находит все строки для заказчиков в San Jose и формирует набор значений snum. Внешний запрос выбирает строки из таблицы Salespeople, где эти значения snum найдены.
   В этом примере, строки для продавцов Rifkin и Serres, которые назначены заказчикам в San Jose - Liu и Cisneros, будут вставлены в таблицу SJpeople.
### НЕ ВСТАВЛЯЙТЕ ДУБЛИКАТЫ СТРОК
   Последовательность команд в предшествующем разделе может быть проблематичной. Продавец Serres находится в San Jose, и следовательно будет вставлен с помощью первой команды. Вторая команда попытается вставить его снова, поскольку он имеет еще одного заказчика в San Jose.
   Если имеются любые ограничения в таблице SJpeople которые вынуждают ее значения быть уникальными, эта вторая вставка потерпит неудачу (как это и должно было быть). Двойные строки это плохо. (См. Главу 18 для подробностей об ограничениях. )
   Было бы лучше если бы вы могли как-то выяснить, что эти значения уже были вставлены в таблицу, прежде чем вы попытаетесь сделать это снова, с помощью добавления другого подзапроса (использующего операторы типа EXISTS, IN, < > ALL, и так далее ) к предикату.
   К сожалению, чтобы сделать эту работу, вы должны будете сослаться на саму таблицу SJpeople в предложении FROM этого нового подзапроса, а, как мы говорили ранее, вы не можете ссылаться на таблицу которая задействована (целиком ) в любом подзапросе команды модификации. В случае INSERT, это будет также препятствовать соотнесенным подзапросам, основанным на таблице в которую вы вставляете значения. Это имеет значение, потому что, с помощью INSERT, вы создаете новую строку в таблице. "Текущая строка" не будет существовать до тех пор, пока INSERT не закончит ее обрабатывать.
### ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ СОЗДАНЫХ ВО ВНЕШНЕЙ ТАБЛИЦЕ ЗАПРОСА
   Запрещение на ссылку к таблице которая модифицируется командой INSERT не предохранит вас от использования подзапросов которые ссылаются к таблице используемой в предложении FROM внешней команды SELECT. Таблица из которой вы выбираете значения, чтобы произвести их для INSERT, не будет задействована командой; и вы сможете ссылаться к этой таблице любым способом которыми вы обычно это делали, но только если эта таблица указана в автономном запросе. Предположим что мы имеем таблицу с именем Samecity в которой мы запомним продавцов с заказчиками в их городах.
   Мы можем заполнить таблицу используя соотнесенный подзапрос:
 
    INSERT INTO (Samecity
    SELECT *
    FROM (Salespeople outer
    WHERE city IN
    ( SELECT city
    FROM Customers inner
    WHERE inner.snum=outer.snum );
 
   Ни таблица Samecity, ни таблица Продавцов не должны быть использованы во внешних или внутренних запросах INSERT. В качестве другого примера, предположим, что вы имеете премию для продавца который имеет самый большой порядок на каждый день. Вы следите за ним в таблице с именем Bonus, которая содержит поле snum продавцов, поле odate и поле amt. Вы должны заполнить эту таблицу информацией которая хранится в таблице Порядков, используя следующую команду:
 
    INSERT INTO Bonus
    SELECT snum, odate, amt
    FROM Orders a
    WHERE amt=
    ( SELECT MAX (amt)
    FROM Orders b
    WHERE a.odate=b.odate );
 
   Даже если эта команда имеет подзапрос который базируется на той же самой таблице что и внешний запрос, он не ссылается к таблице Bonus, на которую воздействует команда. Что для нас абсолютно приемлемо.
   Логика запроса, естественно, должна просматривать таблицу Порядков, и находить для каждой строки максимум порядка сумм приобретений для этой даты. Если эта величина - такая же как у текущей строки, текущая строка является наибольшим порядком для этой даты, и данные вставляются в таблицу Bonus.
### ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С DELETE
   Вы можете также использовать подзапросы в предикате команды DELETE.
   Это даст вам возможность определять некоторые довольно сложные критерии чтобы установить, какие строки будут удаляться, что важно, так как вы конечно же не захотите по неосторожности удалить нужную строку.
   Например, если мы закрыли наше ведомство в Лондоне, мы могли бы использовать следующий запрос чтобы удалить всех заказчиков назначенных к продавцам в Лондоне:
 
    DELETE
    FROM Customers
    WHERE snum=ANY
    ( SELECT snum
    FROM Salespeople
    WHERE city='London' );
 
   Эта команда удалит из таблицы Заказчиков строки Hoffman и Clemens (назначенных для Peel), и Periera (назначенного к Motika).
   Конечно, вы захотите удостовериться, правильно ли сформирована эта операция, прежде чем удалитт или изменитт строки Peel и Motika.
   Это важно. Обычно, когда мы делаем модификацию в базе данных, котовая повлечет другие модификации, наше первое желание - сделать сначало основное действие, а затем проследить другие, вторичные. Этот пример, покажет, почему более эффективно делать наоборот, выполнив сначала вторичные действия.
   Если, например, вы решили изменить значение поля city ваших продавцов везде, где они переназначены, вы должны рассмотреть всех этих заказчиков более сложным способом.
   Так как реальные базы данных имеют тенденцию развиваться до значительно больших размеров чем наши небольшие типовые таблицы, это может стать серьезной проблемой. SQL может предоставить некоторую помощь в этой области используя механизм справочной целостности (обсужденной в Главе 19 ), но это не всегда доступно и не всегда применимо.
   Хотя вы не можете ссылаться к таблице из которой вы будете удалять строки в предложении FROM подзапроса, вы можете в предикате, сослаться на текущую строку-кандидат этой таблицы - которая является строкой которая в настоящее время проверяется в основном предикате. Другими словами, вы можете использовать соотнесенные подзапросы. Они отличаются от тех соотнесенных подзапросов, которые вы могли использовать с INSERT, в котором они фактически базировались на строках-кандидатах таблицы задействованой в команде, а не на запросе другой таблицы.
 
    DELETE FROM Salespeople
    WHERE EXISTS
    ( SELECT *
    FROM Customers
    WHERE rating=100
    AND Salespeople.snum=Customers.snum );
 
   Обратите внимание, что AND часть предиката внутреннего запроса ссылается к таблице Продавцов. Это означает что весь подзапрос будет выполняться отдельно для каждой строки таблицы Продавцов, также как это выполнялось с другими соотнесенными подзапросами. Эта команда удалит всех продавцов которые имели по меньшей мере одного заказчика с оценкой 100 в таблице Продавцов.
   Конечно же, имеется другой способ сделать то же:
 
    DELETE FROM Salespeople
    WHERE 100 IN
    ( SELECT rating
    FROM Customers
    WHERE Salespeople.snum=Customers.snum);
 
   Эта команда находит все оценки для каждого заказчика продавцов и удаляет тех продавцов заказчики которого имеют оценку=100.
   Обычно соотнесенные подзапросы - это подзапросы связанные с таблицей к которой они ссылаются во внешнем запросе (а не в самом предложении DELETE) - и также часто используемы. Вы можете найти наинизший порядок на каждый день и удалить продавцов которые произвели его, с помощью следующей команды:
 
    DELETE FROM Salespeople
    WHERE (snum IN
    ( SELECT snum
    FROM Orders
    WHERE amt=
    ( SELECT MIN (amt)
    FROM Orders b
    WHERE a.odate=b.odate ));
 
   Подзапрос в предикате DELETE, берет соотнесенный подзапрос. Этот внутренний запрос находит минимальный порядок суммы приобретеий для даты каждой строки внешнего запроса. Если эта сумма такая же как сумма текущей строки, предикат внешнего запроса верен, что означает, что текущая строка имеет наименьший порядок для этой даты. Поле snum продавца, ответственного за этот порядок, извлекается и передается в основной предикат команды DELETE, которая затем удаляет все строки с этим значением поля snum из таблицы Продавцов( так как snum - это первичный ключ таблицы Продавцов, то естественно там должна иметься только одна удаляемая строка для значения поля snum выведенного с помощью подзапроса. Если имеется больше одной строки, все они будут удалены. ) Поле snum=1007 которое будет удалено, имеет наименьшее значение на 3 Октября; поле snum =1002, наименьшее на 4 Октября; поле snum=1001, наименьшее в порядках на 5 Октября (эта команда кажется довольно резкой, особенно когда она удаляет Peel создавшего единственный порядок на 5 Октября, но зато это хорошая иллюстрация). Если вы хотите сохранить Peel, вы могли бы добавить другой подзапрос, который бы это делал:
 
    DELETE FROM Salespeople
    WHERE (snum IN
    ( SELECT snum
    FROM Orders a
    WHERE amt=
    ( SELECT MIN (amt)
    FROM Orders b
    WHERE a.odate=b.odate )
    AND 1 <
    ( SELECT COUNT onum
    FROM Orders b
    WHERE a.odate=b.odate ));
 
   Теперь для дня в котором был создан только один порядок, будет произведен счет=1 во втором соотнесенном подзапросе. Это сделает предикат внешнего запроса неправильным, и поля snum следовательно не будут переданы в основной предикат.
### ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С UPDATE
   UPDATE использует подзапросы тем же самым способом что и DELETE - внутри этого необязательного предиката. Вы можете использовать соотнесенные подзапросы или в форме пригодной для использования с DELETE - связаной или с модифицируемой таблицей или с таблицей вызываемой во внешнем запросе. Например, с помощью соотнесенного подзапроса к таблице которая будет модифицироваться, вы можете увеличить комиссионные всех продавцов которые были назначены по крайней мере двум заказчикам:
 
    UPDATE Salespeople
    SET comm=comm + .01
    WHERE 2 <=
    ( SELECT COUNT (cnum)
    FROM Customers
    WHERE Customers.snum=
    Salespeople.snum );

   Теперь продавцы Peel и Serres, имеющие многочисленых заказчиков, получат повышение своих комиссионных.
   Имеется разновидность последнего примера из предыдущего раздела с DELETE. Он уменьшает комиссионные продавцов которые произвели наименьшие порядки, но не стирает их в таблице:
 
    UPDATE Salespeople
    SET comm=comm - .01
    WHERE snum IN
    ( SELECT snum
    FROM Orders a
    WHERE amt=
    ( SELECT MIN (amt)
    FROM Orders b
    WHERE a.odate=b.odate ));
### СТОЛКНОВЕНИЕ С ОГРАНИЧЕНИЯМИ ПОДЗАПРОСОВ КОМАНДЫ DML
   Неспособность сослаться к таблице задействованой в любом подзапросе из команды модификации (UPDATE), устраняет целые категории возможных действий. Например, вы не можете просто выполнить такую операцию как удаление всех заказчиков с оценками ниже средней. Вероятно лучше всего вы могли бы сначала (Шаг 1.), выполнить запрос, получающий среднюю величину, а затем (Шаг 2.), удалить все строки с оценкой ниже этой величины:
   
   
   Шаг 1.
 
    SELECT AVG (rating)
    FROM Customers;
 
   Вывод=200.
 
   Шаг 2.
 
    DELETE
    FROM Customers
    WHERE rating < 200;
### РЕЗЮМЕ
   Теперь вы овладели тремя командами которые управляют всем содержанием вашей базы данных. Осталось только несколько общих вопросов относительно ввода и стирания значений таблицы, когда например эти команды могут выполниться данным пользователем в данной таблице и когда действия сделанные ими, становятся постоянными. Подведем итог: Вы используете команду INSERT чтобы добавлять строки в таблицу. Вы можете или дать имена значениям этих строк в предложении VALUES (когда только одна строка может быть добавлена ), или вывести значения с помощью запроса (когда любое число строк можно добавить одной командой). Если используется запрос, он не может ссылаться к таблице в которую вы делаете вставку,каким бы способом Вы ее ни делали, ни в предложении FROM, ни с помощью внешней ссылки (как это делается в соотнесенных подзапросах). Все это относится к любым подзапросам внутри этого запроса.
   Запрос, однако, оставляет вам свободу использования соотнесенных подзапросов или подзапросов которые дают в предложении FROM имя таблице, которое уже было указано в предложении FROM внешнего запроса (это - общий случай для запросов).
   DELETE и UPDATE используются чтобы, соответственно удалить строки из таблицы и изменить в них значения. Оба они применимы ко всем строкам таблицы, если не используется предикат определяющий какие строки должны быть удалены или модифицированы. Этот предикат может содержать подзапросы, которые могут быть связаны с таблицей, удаляемой, или модифицированой, с помощью внешней ссылки. Эти подзапросы, однако, не могут ссылать к таблице модифицируемой любым предложением FROM. Может показаться, что мы прошли материал SQL который обладает не самым понятным логическим порядком. Сначала мы сделали запрос таблицы которая уже заполнена данными. Потом мы показали как можно фактически помещать эти значения изначально. Но, как вы видете, полное ознакомление с запросами здесь неоценимо. Теперь, когда мы показали вам как заполнять значениями таблицы которые уже были созданы (по определению), мы покажем(со следующей главы) откуда появились эти таблицы.
### РАБОТА С SQL
   * Предположите, что имеется таблица называемая Multicust, с такими же именами столбцов что и таблица Продавцов. Напишите команду, которая бы вставила всех продавцов (из таблицы Продавцов)имеющих более чем одного заказчика в эту таблицу.
   * Напишите команду которая бы удаляла всех заказчиков не имеющих текущих порядков.
   * Напишите команду которая бы увеличила на двадцать процентов комиссионные всех продавцов имеющих общие текущие порядки выше чем $3,000.

### Глава 17. СОЗДАНИЕ ТАБЛИЦ
 
   ВПЛОТЬ ДО ЭТОГО МЕСТА, МЫ ЗАПРАШИВАЛИ ТАБЛИЦЫ данных и выполняли команды по извлечению этих данных, считая, что эти таблицы уже были созданы кем - то до нас . Это действительно наиболее реальная ситуация, когда небольшое колличество людей создают таблицы, которые затем используются другими людьми. Наша цель состоит в том, чтобы охватив информацию сначала более широко, перейти затем к более узким вопросам.
 
   В этой главе, мы будем обсуждать создание, изменение, и удаление таблиц. Все это относится к самим таблицам, а не к данным которые в них содержатся. Будете или не будете Вы выполнять эти операции самостоятельно, но их концептуальное понимание увеличит ваше понимание языка SQL и природу таблиц которые вы используете. Эта глава вводит нас в область SQL называемую - DDL(Язык Определения Данных), где создаются объекты данных SQL.
   Эта глава также покажет другой вид объекта данных SQL - Индекс. Индексы используются чтобы делать поиск более эффективным и, иногда, заставлять значения отличаться друга от друга. Они обычно работают незаметно для Вас, но если вы попробуете поместить значения в таблицу и они будут отклонены, из-за их неуникальности, это будет означать что другая строка имеет то же самое значение для этого поля, и что это поле имеет уникальный индекс или ограничение которое предписывает ему уникальность. Обсуждение вышеупомянутого, продолжится в Главе 18.
### КОМАНДА СОЗДАНИЯ ТАБЛИЦЫ
   Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу - таблицу без строк. Значения вводятся с помощью DML команды INSERT (См. Главу 15). Команда CREATE TABLE в основном определяет имя таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.
   Синтаксис команды CREATE TABLE:
 
    CREATE TABLE <table-name >
    ( <column name > <data type>[(<size>)],
    <column name > <data type> [(<size>)] ... );
 
   Как сказано в Главе 2, типы данных значительно меняются от программы к программе. Для совместимости со стандартом, они должны все, по крайней мере, поддерживать стандарт типа ANSI. Он описан в Приложении B. Так как пробелы используются для разделения частей команды SQL, они не могут быть частью имени таблицы (или любого другого объекта, такого как индекс). Подчеркивание (_ ) - обычно используется для разделения слов в именах таблиц.
   Значение аргумента размера зависит от типа данных. Если вы его не указываете, ваша система сама будет назначать значение автоматически.
   Для числовых значений, это - лучший выход, потому что в этом случае, все ваши поля такого типа получат один и тот же размер что освобождает вас от проблем их общей совместимости(см. Главу 14). Кроме того, использование аргумента размера с некоторыми числовым наборами, не совсем простой вопрос. Если вам нужно хранить большие числа, вам несомненно понадобятся гарантии, что поля достаточно велики чтобы вместить их.
   Один тип данных для которого вы, в основном, должны назначать размер - CHAR. Аргумент размера - это целое число которое определяет максимальное число символов которое может вместить поле. Фактически, число символов поля может быть от нуля (если поле - NULL ) до этого числа.
   По умолчанию, аргумент размера=1, что означает что поле может содержать только одну букву. Это конечно не совсем то что вы хотите.
   Таблицы принадлежат пользователю который их создал, и имена всех таблиц принадлежащих данному пользователю должны отличаться друга от друга, как и имена всех столбцов внутри данной таблицы. Отдельные таблицы могут использовать одинаковые имена столбцов, даже если они принадлежат одному и тому же пользователю. Примером этому - столбец city в таблице Заказчиков и в таблице Продавцов. Пользователи не являющиеся владельцами таблиц могут ссылаться к этим таблицам с помощью имени владельца этих таблиц сопровождаемого точкой; например, таблица Employees созданая Smith будет называться Smith.Employees когда она упоминается каким-то другим пользователем (мы понимаем что Smith - это Идентификатор Разрешения (ID). (ID) сообщаемый пользователем (ваш разрешенный ID - это ваше имя в SQL. Этот вывод обсуждался в Главе 2, и будет продолжен в Главе 22 ).
 
   Эта команда будет создавать таблицу Продавцов:
 
    CREATE TABLE Saleepeople
    ( snum integer,
    sname char (10),
    city char (10),
    comm declmal );
 
   Порядок столбцов в таблице определяется порядком в котором они указаны. Имя столбца не должно разделяться при переносе строки (что сделано для удобочитаемости ), но отделяется запятыми.
### ИНДЕКСЫ
   Индекс - это упорядоченный (буквенный или числовой ) список столбцов или групп столбцов в таблице. Таблицы могут иметь большое колличество строк, а, так как строки не находятся в каком-нибудь определенном порядке, на их поиск по указанному значению может потребовать время.
   Индексный адрес - это и забота, и в то же время обеспечение способа объединения всех значений в группы из одной или больше строк, которые отличаются одна от другой. В Главе 18, мы будем описывать более непосредственный способ который заставит ваши значения быть уникальными. Но этот метод не существует в ранних версиях SQL. Так как уникальность часто необходима, индексы и использовались с этой целью.
   Индексы - это средство SQL, которое родил сам рынок, а не ANSI. Поэтому, сам по себе стандарт ANSI в настоящее время не поддерживает индексы, хотя они очень полезны и широко применяемы.
   Когда вы создаете индекс в поле, ваша база данных запоминает соответствующий порядок всех значений этого поля в области памяти. Предположим что наша таблица Заказчиков имеет тысячи входов, а вы хотите найти заказчика с номером=2999. Так как строки не упорядочены, ваша программа будет просматривать всю таблицу, строку за строкой, проверяя каждый раз значение поля cnum на равенство значению 2999. Однако, если бы имелся индекс в поле cnum, то программа могла бы выйти на номер 2999 прямо по индексу и дать информацию о том как найти правильную строку таблицы.
   В то время как индекс значительно улучшает эффективность запросов, использование индекса несколько замедляет операции модификации DML(такие как INSERT и DELETE ), а сам индекс занимает объем памяти. Следовательно, каждый раз когда вы создаете таблицу Вы должны принять решение, индексировать ее или нет. Индексы могут состоять из многочисленых полей. Если больше чем одно поле указыватся для одного индекса, второе упорядочивается внутри первого, третье внутри второго, и так далее. Если вы имели первое и последнее имя в двух различных полях таблицы, вы могли бы создать индекс который бы упорядочил предыдущее поле внутри последующего. Это может быть выполнено независимо от способа упорядочивания столбцов в таблице.
   Синтаксис для создания индекса - обычно следующий (помните, что это не ANSI стандарт ):
 
    CREATE INDEX <index name> ON <table name>
    (<column name> [,<column name>]...);
 
   Таблица, конечно, должна уже быть создана и должна содержать имя столбца. Имя индекса не может быть использовано для чего-то другого в базе данных (любым пользователем). Однажды созданый, индекс будет невидим пользователю. SQL сам решает когда он необходим чтобы ссылаться на него и делает это автоматически. Если, например, таблица Заказ чиков будет наиболее часто упоминаемой в запросах продавцов к их собственной клиентуре, было бы правильно создать такой индекс в поле snum таблицы Заказчиков.
 
    CREATE INDEX Clientgroup ON Customers (snum);
   Теперь, тот продавец который имеет отношение к этой таблицы сможет найти собственную клиентуру очень быстро.
### УНИКАЛЬНОСТЬ ИНДЕКСА
   Индексу в предыдущем примере, к счастью, не предписывается уникальность, несмотря на наше замечание, что это является одним из назначений индекса. Данный продавец может иметь любое число заказчиков. Однако, этого не случится если мы используем ключевое слово UNIQUE перед ключевым словом INDEX. Поле сnum, в качестве первичного ключа, станет первым кандидатом для уникального индекса:
 
    CREATE UNIQUE INDEX Custid ON Customers (cnum);
   > ПРИМЕЧАНИЕ: эта команда будет отклонена если уже имеются идентичные значения в поле cnum. Лучший способ иметь дело с индексами состоит в том, чтобы создавать их сразу после того, как таблица создана и прежде, чем введены любые значения. Так же обратите внимание что, для уникального индекса более чем одного поля, это - комбинация значений, каждое из которых, может и не быть уникальным.

Предыдущий пример - косвенный способ заставить поле cnum работать как первичный ключ таблицы Заказчиков. Базы данных воздействуют на первичные и другие ключи более непосредственно. Мы будем обсуждать этот вывод далее в Главах 18 и 19.
### УДАЛЕНИЕ ИНДЕКСОВ
   Главным признаком индекса является его имя - поэтому он может быть удален. Обычно пользователи не знают о существовании индекса. SQL автоматически определяет позволено ли пользователю использовать индекс, и если да, то разрешает использовать его. Однако, если вы хотите удалить индекс, вы должны знать его имя. Этот синтаксис используется для удаления индекса:
 
    DROP INDEX <Index name>;
 
   Удаление индекса не воздействует на содержание полей.
   ИЗМЕНЕНИЕ ТАБЛИЦЫ ПОСЛЕ ТОГО, КАК ОНА БЫЛА СОЗДАНА
   Команда ALTER TABLE не часть стандарта ANSI; но это - широко доступная, и довольно содержательная форма, хотя ее возможности несколько ограничены. Она используется чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения (обсужденные в Главе 18). Типичный синтаксис чтобы добавить столбец к таблице :
 
    ALTER TABLE <table name> ADD <column name>
    <data type> <size>;
 
   Столбец будет добавлен со значением NULL для всех строк таблицы. Новый столбец станет последним по порядку столбцом таблицы. Вообще то, можно добавить сразу несколько новых столбцов, отделив их запятыми, в однй команде. Имеется возможность удалять или изменять столбцы. Наиболее часто, изменением столбца может быть просто увеличение его размера, или добавление( удаление ) ограничения. Ваша система должна убедиться, что любые изменения не противоречат существующим данным - например при попытке добавить ограничение к столбцу который уже имел значение при нарушении которого ограничение будет отклонено. Лучше всего дважды проверить это. По крайней мере, посмотрите документацию вашей системы чтобы убедиться, гарантирует ли она что именно это было причиной. Из-за нестандартного характера команды ALTER TABLE, вам все равно необходимо посмотреть тот раздел вашей системной документации где говорится об особых случаях.
   ALTER TABLE - не действует, когда таблица должна быть переопределена, но вы должны разрабатывать вашу базу данных по возможности так чтобы не слишком ей в этом передоверяться. Изменение структуры таблицы когда она уже в использовании - опасно! Просмотрите внимательно таблицы, которые являясь вторичными таблицами с извлечеными данными из другой таблицы (смотри Главу 20 ), не долго правильно работают, а программы использующие вложенный SQL( Глава 25 ) выполняются неправильно или не всегда проавильно. Кроме того, изменение может стереть всех пользователей имеющих разрешение обращаться к таблице. По этим причинам, вы должны разрабатывать ваши таблицы так, чтобы использовать ALTER TABLE только в крайнем случае.
   Если ваша система не поддерживает ALTER TABLE, или если вы хотите избежать ее использования, вы можете просто создать новую таблицу, с необходимыми изменениями при создании, и использовать команду INSERT с SELECT * запросом чтобы переписать в нее данные из старой таблицы.
   Пользователям которым был предоставлен доступ к старой таблице (см. Главу 22 ) должен быть предоставлен доступ к новой таблице.
### УДАЛЕНИЕ ТАБЛИЦ
   Вы должны быть собственником( т.е. быть создателем ) таблицы чтобы иметь возможность удалить ее. Поэтому не волнуйтесь о случайном разрушении ваших данных, SQL сначала потребует чтобы вы очистили таблицу прежде, чем удалит ее из базы данных. Таблица с находящимися в ней строками, не может быть удалена. Обратитесь к Главе 15 за подробностями относительно того как удалять строки из таблицы. Синтаксис для удаления вашей таблицы, если конечно она является пустой, следующая:
 
    DROP TABLE < table name >;
 
   При подаче этой команды, имя таблицы больше не распознается и нет такой команды которая могла быть дана этому объекту. Вы должны убедиться, что эта таблица не ссылается внешним ключом к другой таблице(Внешние ключи обсуждаются в Главе 19 ), и что она не используется в определении Представления( Глава 20 ).
   Эта команда фактически не является частью стандарта ANSI, но она обще поддерживаема и полезна. К счастью, она более проста, и следовательно более непротиворечива, чем ALTER TABLE . ANSI просто не имеет способа для определения разрушеных или неправильных таблиц.
### РЕЗЮМЕ
   Теперь Вы уже бегло ориентируетесь в основах определений данных. Вы можете создавать, изменять, и удалять таблицы. В то время как только первая из этих функций - часть официального стандарта SQL, другие будут время от времени меняться, особенно - ALTER TABLE. DROP TABLE позволяет вам избавиться от таблиц которые бесполезны. Она уничтожает только пустые таблицы, и следовательно не разрушает данные.
   Вы теперь знаете об индексах а также, как их создавать и удалять.
   SQL не дает вам большого управления над ими, так как реализация которую вы используете довольно удачно определяет, как быстро выполняются различные команды. Индексы - это один из инструментов дающий Вам возможность воздействовать непосредственно на эффективность ваших команд в SQL. Мы рассмотрели индексы здесь чтобы отличать их от ограничений, с которыми их нельзя путать. Ограничения - это тема Главы 18 и Главы 19.
### РАБОТА С SQL
   * Напишите предложение CREATE TABLE которое бы вывело нашу таблицу Заказчиков.
   * Напишите команду которая бы давала возможность пользователю быстро извлекать порядки сгруппированные по датам из таблицы Порядков.
   * Если таблица Порядков уже создана, как Вы можете заставить поле onum быть уникальным (если допустить что все текущие значения уникальны)?
   * Создайте индекс который бы разрешал каждому продавцу быстро отыскивать его порядки сгруппированные по датам.
   * Предположим, что каждый продавец имеет только одного заказчика с данной оценкой, введите команду которая его извлечет.

### Глава 18.ОГРАНИЧЕНИЕ ЗНАЧЕНИЙ ВАШИХ ДАННЫХ
 
   В ГЛАВЕ 17, ВЫ УЗНАЛИ КАК СОЗДАЮТСЯ ТАБЛИЦЫ. Теперь более тщательно с этого места мы покажем вам как вы можете устанавливать ограничения в таблицах. Ограничения - это часть определений таблицы, которое ограничивает значения которые вы можете вводить в столбцы. До этого места в книге, единственым ограничением на значения которые вы могли вводить, были тип данных и размер вводимых значений которые должны быть совместимы с теми столбцами в которые эти значения помещаются (как и определено в команде CREATE TABLE или команде ALTER TABLE). Ограничения дают вам значительно большие возможности и скоро вы это увидете. Вы также узнаете как определять значения по умолчанию в этой главе. По умолчанию - это значение которое вставляется автоматически в любой столбец таблицы, когда значение для этого столбца отсутствует в команде INSERT для этой таблицы. NULL - это наиболее широко используемое значение по умолчанию, но в этой главе будет показано как определять и другие значения по умолчанию.
 
### ОГРАНИЧЕНИЕ ТАБЛИЦ
   Когда вы создаете таблицу (или, когда вы ее изменяете ), вы можете помещать ограничение на значения которые могут быть введены в поля.
   Если вы это сделали, SQL будет отклонять любые значения которые нарушают критерии которые вы определили. Имеется два основных типа ограничений - ограничение столбца и ограничение таблицы. Различие между ними в том, что ограничение столбца применяется только к индивидуальным столбцам, в то время как ограничение таблицы применяется к группам из одного и более столбцов.
### ОБЪЯВЛЕНИЕ ОГРАНИЧЕНИЙ
   Вы вставляете ограничение столбца в конец имени столбца после типа данных и перед запятой. Ограничение таблицы помещаются в конец имени таблицы после последнего имени столбца, но перед заключительной круглой скобкой. Далее показан синтаксис для команды CREATE TABLE, расширенной для включения в нее ограничения:
 
    CREATE TABLE < table name >
    (< column name > <data type > < column constraint >,
    < column name > < data type > < column constraint > ...
    < table constraint > (< column name >
    [, < column name > ])... );
 
   ( Для краткости, мы опустили аргумент размера, который иногда используется с типом данных. ) Поля данные в круглых скобках после ограничения таблицы - это поля к которым применено это ограничение. Ограничение столбца, естественно, применяется к столбцам, после чьих имен оно следует. Остальная часть этой глава будет описывать различные типы ограничений и их использование.
   ИСПОЛЬЗОВАНИЕ ОГРАНИЧЕНИЙ ДЛЯ ИСКЛЮЧЕНИЯ ПУСТЫХ( NULL ) УКАЗАТЕЛЕЙ
   Вы можете использовать команду CREATE TABLE чтобы предохранить поле от разрешения в нем пустых(NULL) указателей с помощью ограничения NOT NULL. Это ограничение накладывается только для разнообразных столбцов.
   Вы можете вспомнить что NULL - это специальное обозначение которое отмечает поле как пустое. NULL может быть полезен, когда имеются случаи, когда вы хотите быть от них гарантированы. Очевидно, что первичные ключи никогда не должны быть пустыми, поскольку это будет подрывать их функциональные возможности. Кроме того, такие поля как имена, требуют в большинстве случаев, определенных значений. Например, вы вероятно захотите иметь имя для каждого заказчика в таблице Заказчиков.
   Если вы поместите ключевые слова NOT NULL сразу после типа данных (включая размер ) столбца, любая попытка поместить значение NULL в это поле будет отклонена. В противном случае, SQL понимает, что NULL разрешен.
   Например, давайте улучшим наше определение таблицы Продавцов, не позволяя помещать NULL значения в столбцы snum или sname :
 
    CREATE TABLE Salespeople
    ( Snum integer NOT,
    Sname char (10) NOT,
    city char (10),
    comm decimal);
 
   Важно помнить, что любому столбцу с ограничением NOT NULL должно быть установлено значение в каждом предложении INSERT воздействующем на таблицу. При отсутствии NULL, SQL может не иметь значений для установки в эти столбцы, если конечно значение по умолчанию, описанное ранее в этой главе, уже не было назначено.
   Если ваша система поддерживает использование ALTER TABLE чтобы добавлять новые столбцы к уже существующей таблице, вы можете вероятно помещать ограничение столбцов, типа NOT NULL, для этих новых столбцов.
   Однако, если вы предписываете новому столбцу значение NOT NULL, теку-щая таблица должна быть пустой.
### УБЕДИТЕСЬ ЧТО ЗНАЧЕНИЯ - УНИКАЛЬНЫ
   В Главе 17, мы обсудили использование уникальных индексов чтобы заставить поля иметь различные значения для каждой строки. Эта практика - осталась с прежних времен, когда SQL поддерживал ограничение UNIQUE.
   Уникальность - это свойство данных в таблице, и поэтому его более логично назвать как ограничение этих данных, а не просто как свойство логического отличия, связывающее объект данных (индекс ).
   Несомненно, уникальные индексы - один из самых простых и наиболее эффективных методов предписания уникальности. По этой причине, некоторые реализации ограничения UNIQUE используют уникальные индексы; то-есть они создают индекс не сообщая вам об этом. Остается фактом, что вероятность беспорядка в базе данных достаточно мала, если вы предписываете уникальность вместе с ограничением.

### УНИКАЛЬНОСТЬ КАК ОГРАНИЧЕНИЕ СТОЛБЦА
   Время от времени, вы хотите убедиться, что все значения введеные в столбец отличаются друг от друга. Например, первичные ключи достаточно ясно это показывают. Если вы помещаете ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк, значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям которые были обьявлены как непустые(NOT NULL), так как не имеет смысла позволить одной строке таблицы иметь значение NULL, а затем исключать другие строки с NULL значениями как дубликаты. Имеется дальнейшее усовершенствование нашей команды создания таблицы Продавцов :
 
    CREATE TABLE Salespeople
    ( Snum integer NOT NULL UNIQUE,
    Sname char (10) NOT NULL UNIQUE,
    city char (10),
    comm decimal );
 
   Когда вы обьявляете поле sname уникальным, убедитесь, что две Mary Smith будут введены различными способами - например, Mary Smith и M. Smith. В то же время это не так уж необходимо с функциональной точки зрения - потому что поле snum в качестве первичного ключа, все равно обеспечит отличие этих двух строк - что проще для людей использующих данные в таблицах, чем помнить что эти Smith не идентичны. Столбцы (не первичные ключи ) чьи значения требуют уникальности, называются ключами-кандидатами или уникальными ключами.
### УНИКАЛЬНОСТЬ КАК ОГРАНИЧЕНИЕ ТАБЛИЦЫ
   Вы можете также определить группу полей как уникальную с помощью команды ограничения таблицы - UNIQUE. Объявление группы полей уникаль ной, отличается от объявления уникальными индивидуальных полей, так как это комбинация значений, а не просто индивидуальное значение, которое обязано быть уникальным. Уникальность группы - это представление порядка, так что бы пары строк со значениями столбцов "a", "b" и "b", "a" рассматривались отдельно одна от другой. Наша база данных сделана так чтобы каждый заказчик был назначен одному и только одному продавцу. Это означает что каждая комбинация номера заказчика(cnum) и номера продавца(snum) в таблице Заказчиков должна быть уникальной. Вы можете убедиться в этом, создав таблицу Заказчиков таким способом:
 
    CREATE TABLE Customers
    ( cnum integer NOT NULL,
    cname char (10) NOT NULL,
    city char (10),
    rating integer,
    snum integer NOT NULL,
    UNIQUE (cnum, snum));
 
   Обратите внимание что оба поля в ограничении таблицы UNIQUE все еще используют ограничение столбца - NOT NULL . Если бы мы использовали ограничение столбца UNIQUE для поля cnum, такое ограничение таблицы было бы необязательным. Если значения поля cnum различно для каждой строки, то не может быть двух строк с идентичной комбинацией значений полей cnum и snum. То же самое получится если мы обьявим поле snum уникальным, хотя это и не будет соответствовать нашему примеру, так как продавец будет назначен многочисленым заказчикам. Следовательно, ограничение таблицы - UNIQUE, наиболее полезно когда вы не хотите заставлять индивидуальные поля быть уникальными.
   Предположим, например, что мы разработали таблицу чтобы следить за всеми порядками каждый день для каждого продавца. Каждая строка такой таблицы представляет сумму чисел любых порядков, а не просто индивидуальный порядок. В этом случае, мы могли бы устранить некоторые возможные ошибки убедившись что на каждый день имеется не более чем одна строка для данного продавца, или что каждая комбинация полей snum и odate является уникальной. Вот как например мы могли бы создать таблицу с именем Salestotal :
 
    CREATE TABLE Salestotal
    ( cnum integer NOT NULL,
    odate date NULL,
    totamt decimal,
    UNIQUE (snum, odate));
 
   Кроме того, имеется команда которую вы будете использовать чтобы помещать текущие данные в эту таблицу:
 
    INSERT INTO Salestotal
    SELECT snum, odate, SUM (amt)
    FROM Orders
    GROUP BY snum, odate;
### ОГРАНИЧЕНИЕ ПЕРВИЧНЫХ КЛЮЧЕЙ
   До этого мы воспринимали первичные ключи исключительно как логические понятия. Хоть мы и знаем что такое первичный ключ, и как он должен использоваться в любой таблице, мы не ведаем "знает" ли об этом SQL.
   Поэтому мы использовали ограничение UNIQUE или уникальные индексы в первичных ключах чтобы предписывать им уникальность. В более ранних версиях языка SQL, это было необходимо, и могло выполняться этим способом. Однако теперь, SQL поддерживает первичные ключи непосредственно с ограничением Первичный Ключ (PRIMARE KEY). Это ограничение может быть доступным или недоступным вашей системе.
   PRIMARY KEY может ограничивать таблицы или их столбцы. Это ограничение работает так же как и ограничение UNIQUE, за исключением когда только один первичный ключ (для любого числа столбцов ) может быть определен для данной таблицы. Имеется также различие между первичными ключами и уникальностью столбцов в способе их использоваться с внешними ключами, о которых будет рассказано в Главе 19. Синтаксис и определение их уникальности те же что и для ограничения UNIQUE.
   Первичные ключи не могут позволять значений NULL. Это означает что, подобно полям в ограничении UNIQUE, любое поле используемое в ограничении PRIMARY KEY должно уже быть обьявлено NOT NULL. Имеется улучшеный вариант создания нашей таблицы Продавцов :
 
    CREATE TABLE Salestotal
    ( snum integer NOT NULL PRIMARY KEY,
    sname char(10) NOT NULL UNIQUE,
    city char(10),
    comm decimal);
 
   Как вы видете, уникальность (UNIQUE) полей может быть обьявлена для той же самой таблицы. Лучше всего помещать ограничение PRIMARY KEY в поле(или в поля) которое будет образовывать ваш уникальный идентификатор строки, и сохранить ограничение UNIQUE для полей которые должны быть уникальными логически (такие как номера телефона или поле sname ), а не для идентификации строк.
### ПЕРВИЧНЫЕ КЛЮЧИ БОЛЕЕ ЧЕМ ОДНОГО ПОЛЯ
   Ограничение PRIMARY KEY может также быть применено для многочисленных полей, составляющих уникальную комбинацию значений. Предположим что ваш первичный ключ - это имя, и вы имеете первое имя и последнее имя сохраненными в двух различных полях (так что вы можете организовывать данные с помощью любого из них). Очевидно, что ни первое ни последнее имя нельзя заставить быть уникальным самостоятельно, но мы можем каждую из этих двух комбинаций сделать уникальной.
   Мы можем применить ограничение таблицы PRIMARY KEY для пар:
 
    CREATE TABLE Namefield
    ( firstname char (10) NOT NULL,
    lastname char (10) NOT NULL
    city char (10),
    PRIMARY KEY (firstname, lastname ));
 
   Одна проблема в этом подходе та, что мы можем вынудить появление уникальности - например, введя Mary Smith и M. Smith. Это может ввести в заблуждение, потому что ваши служащие могут не знать кто из них кто.
   Обычно более надежный способ чтобы определять числовое поле которое могло бы отличать одну строку от другой, это иметь первичный ключ, и применять ограничение UNIQUE для двух имен полей.
### ПРОВЕРКА ЗНАЧЕНИЙ ПОЛЕЙ
   Конечно, имеется любое число ограничений которые можно устанавливать для данных вводимых в ваши таблицы, чтобы видеть, например, находятся ли данные в соответствующем диапазоне или правильном формате, о чем SQL естественно не может знать заранее. По этой причине, SQL обеспечивает вас ограничением CHECK, которое позволяет вам установить условие которому должно удовлетворять значение вводимое в таблицу, прежде чем оно будет принято. Ограничение CHECK состоит из ключевого слова CHECK сопровождаемого предложением предиката, который использует указанное поле. Любая попытка модифицировать или вставить значение поля которое могло бы сделать этот предикат неверным - будет отклонена.
   Давайте рассмотрим еще раз таблицу Продавцов. Столбец комиссионных выражается десятичным числом и поэтому может быть умножен непосредственно на сумму приобретений в результате чего будет получена сумма комиссионных(в долларах) продавца с установленым справа значком доллара( $ ) . Кто-то может использовать понятие процента, однако ведь, можно об этом и не знать. Если человек введет по ошибке 14 вместо .14 чтобы указать в процентах свои комиссионные, это будет расценено как 14.0, что является законным десятичным значением, и будет нормально воспринято системой. Чтобы предотвратить эту ошибку, мы можем наложить ограничение столбца - CHECK чтобы убедиться что вводимое значение меньше чем 1.
 
    CREATE TABLE Salespeople
    ( snum integer NOT NULL PRIMARY KEY,
    sname char(10) NOT NULL UNIQUE,
    city char(10),
    comm decimal CHECK (comm < 1 ));
 
### ИСПОЛЬЗОВАНИЕ - CHECK, ЧТОБЫ ПРЕДОПРЕДЕЛЯТЬ ДОПУСТИМОЕ ВВОДИМОЕ ЗНАЧЕНИЕ
   Мы можем также использовать ограничение CHECK чтобы защитить от ввода в поле определенных значений, и таким образом предотвратить ошибку.
   Например, предположим, что единствеными городами в которых мы имели ведомства сбыта являются Лондон, Барселона, Сан Хосе, и Нью Йорк. Если вам известны все продавцы работающие в каждом из этих ведомств, нет необходимости позволять ввод других значений. Если же нет, использование ограничения может предотвратить опечатки и другие ошибки.
 
    CREATE TABLE Salespeople
    (snum integer NOT NULL UNIQUE,
    sname char(10) NOT NULL UNIQUE,
    city char(10) CHECK,
    (city IN ('London', 'New York', 'San Jose', 'Barselona')),
    comm decimal CHECK (comm < 1 ));
 
   Конечно, если вы собираетесь сделать это, вы должны быть уверены что ваша компания не открыла уже новых других ведомств сбыта. Большинство программ баз данных поддерживают команду ALTER TABLE( см. Главу 17 ) которая позволяет вам изменять определение таблицы, даже когда она находится в использовании. Однако, изменение или удаление ограничений не всегда возможно для этих команд, даже там где это вроде бы поддерживается.
   Если вы использовали систему которая не может удалять ограничения, вы будете должны создавать (CREATE) новую таблицу и передавать информацию из старой таблицы в нее всякий раз, когда вы хотите изменить ограничение. Конечно же Вы не захотите делать это часто, и со временем вообще перестанете это делать.
 
   Создадим таблицу Порядков:
 
    CREATE TABLE Orders
    (onum integer NOT NULL UNIQUE,
    amt decimal,
    odate date NOT NULL,
    cnum integer NOT NULL,
    snum integer NOT NULL);
 
   Как мы уже говорили в Главе 2, тип DATЕ(ДАТА) широко поддерживается, но не является частью стандарта ANSI. Что же делать если мы используем базу данных, которая следуя ANSI, не распознает тип DATЕ? Если мы обьявим поле odate любым типом числа, мы не сможем использовать наклонную черту вправо (/) или черточку (-) в качестве разделителя. Так как печатаемые номера - это символы ASCII, мы можем обьявить тип поля odate - CHAR. Основная проблема в том, что мы будем должны использовать одиночные кавычки всякий раз, когда ссылаемся на значение поля odate в запросе. Нет более простого решения этой проблемы там где тип DATЕ стал таким популярным. В качестве иллюстрации, давайте обьявим поле odate - типом CHAR. Мы можем по крайней мере наложить на него наш формат с ограничением CHECK:
 
    CREATE TABLE Orders
    ( onum integer NOT NULL UNIQUE,
    amt decimal,
    odate char (10) NOT NULL CHECK (odate LIKE
    '--/--/----'),
    cnum NOT NULL,
    snum NOT NULL );
 
   Кроме того, если вы хотите, вы можете наложить ограничение, гарантирующие что введенные символы - числа, и что они - в пределах значений нашего диапазона.
### ПРОВЕРКА УСЛОВИЙ БАЗИРУЮЩИЙСЯ НА МНОГОЧИСЛЕНЫХ ПОЛЯХ
   Вы можете также использовать CHECK в качестве табличного ограничения. Это полезно в тех случаях когда вы хотите включить более одного поля строки в условие. Предположим что комиссионные .15 и выше, будут разрешены только для продавца из Барселоны. Вы можете указать это со следующим табличным ограничением CHECK :
 
    CREATE TABLE Salespeople
    ( snum integer NOT NULL UNIQUE,
    sname char (10) NOT NULL UNIQUE,
    city char(10),
    comm decimal,
    CHECK (comm < .15 OR city='Barcelona'));
 
   Как вы можете видеть, два различных поля должны быть проверены чтобы определить, верен предикат или нет. Имейте в виду, что это - два разных поля одной и той же строки. Хотя вы можете использовать многочисленые поля, SQL не может проверить более одной строки одновременно. Вы не можете например использовать ограничение CHECK чтобы удостовериться что все комиссионные в данном городе одинаковы. Чтобы сделать это, SQL должен всякий раз просматривая другие строки таблицы, когда вы модифицируете или вставляете строку, видеть, что значение комиссионных указано для текущего города. SQL этого делать не умеет.
   Фактически, вы могли бы использовать сложное ограничение CHECK для вышеупомянутого, если бы знали заранее, каковы должны быть комиссионные в разных городах. Например, вы могли бы установить ограничение типа этого:
 
    CHECK (( comm=.15 AND clty='London' )
    OR (comm=.14 AND city='Barcelona' )
    OR (comm=11 AND city='San Jose').. )
 
   Вы получили идею. Чем налагать такой комплекс ограничений, вы могли бы просто использовать представление с предложением WITH CHECK OPTION которое имеет все эти условия в своем предикате (смотри Главу 20 и 21 для информации о представлении и о WITH CHECK OPTION). Пользователи могут обращаться к представлению таблицы вместо самой таблицы. Одним из преимуществ этого будет то, что процедура изменения в ограничении не будет такой болезненной или трудоемкой. Представление с WITH CHECK OPTION - хороший заменитель ограничению CHECK, что будет показано в Главе 21.
### УСТАНОВКА ЗНАЧЕНИЙ ПО УМОЛЧАНИЮ
   Когда вы вставляете строку в таблицу без указания значений в ней для каждого поля, SQL должен иметь значение по умолчанию для включения его в определенное поле, или же команда будет отклонена. Наиболее общим значением по умолчанию является - NULL. Это - значение по умолчанию для любого столбца, которому не было дано ограничение NOT NULL или который имел другое назначение по умолчанию.
   Значение DEFAULT(ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE тем же способом что и ограничение столбца, хотя, с технической точки зрения, значение DEFAULT не ограничительного свойства - оно не ограничивает значения которые вы можете вводить, а просто определяет, что может случиться если вы не введете любое из них. Предположим что вы работаете в оффисе Нью Йорка и подавляющее большинство ваших продавцов живут в Нью Йорке. Вы можете указать Нью Йорк в качестве значения поля city, по умолчанию, для вашей таблицы Продавцов:
 
    CREATE TABLE Salespeople
    ( snum integer NOT NULL UNIQUE,
    sname char(10) NOT NULL UNIQUE,
    city char(10) DEFAULT='New York',
    comm decimal CHECK (comm < 1);
 
   Конечно, вводить значение Нью Йорк в таблицу каждый раз когда назначается новый продавец, не такая уж необходимость, и можно просто пренебречь им (не вводя его ) даже если оно должно иметь некоторое значение. Значение по умолчанию такого типа, более предпочтительно, чем, например, длинный конторский номер указывающий на ваше собственное ведомство, в таблице Порядков. Длинные числовые значения - более расположены к ошибке, поэтому если подавляющее большинство (или все ) ваших порядков должны иметь ваш собственный конторский номер, желательно устанавливать для них значение по умолчанию.
   Другой способ использовать значение по умолчанию - это использовать его как альтернативу для NULL. Так как NULL (фактически) неверен при любом сравнении, ином чем IS NULL, он может быть исключен с помощью большинства предикатов. Иногда, вам нужно видеть пустые значения ваших полей не обрабатывая их каким-то определенным образом. Вы можете установить значение по умолчанию, типа нуль или пробел, которые функционально меньше по значению чем просто не установленное значение - пустое значение(NULL). Различие между ними и обычным NULL в том, что SQL будет обрабатывать их также как и любое другое значение.
   Предположим, что заказчикам не назначены оценки изначально. Каждые шесть месяцев, вы повышаете оценку всем вашим заказчикам, имеющим оценку ниже средней, включая и тех кто предварительно не имел никакого назначения оценки. Если вы хотите выбрать всех этих заказчиков как группу, следующий запрос исключит всех заказчиков с оценкой=NULL:
 
    SELECT *
    FROM Customers
    WHERE rating <=100;
 
   Однако, если вы назначили значение поумолчанию=000, в поле rating, заказчики без оценок будут выбраны наряду с другими. Приоритет каждого метода - зависит от ситуации. Если вы будете делать запрос с помощью поля оценки, то захотите ли Вы включить строки без значений, или исключите их?
   Другая характеристика значений по умолчанию этого типа, позволит обьявить Вам поле оценки - как NOT NULL. Если вы используете его поумолчанию, чтобы избежать значений =NULL, то это - вероятно хорошая защита от ошибок.
   Вы можете также использовать ограничения UNIQUE или PRIMARY KEY в этом поле. Если вы сделаете это, то, имеете в виду, что только одна строка одновременно может иметь значение по умолчанию. Любую строку которая содержит значение по умолчанию нужно будет модифицировать прежде, чем другая строка с установкой по умолчанию будет вставлена.
   Это не так как вы обычно используете значения по умолчанию, поэтому ограничения UNIQUE и PRIMARY KEY (особенно последнее ) обычно не устанавливаются для строк со значениями по умолчанию.
### РЕЗЮМЕ
   Вы теперь владеете несколькими способами управления значениями которые могут быть введены в ваши таблицы. Вы можете использовать ограничение NOT NULL чтобы исключать NULL, ограничение UNIQUE чтобы вынуждать все значения в группе из одного или более столбцов отличаться друг от друга, ограничение PRIMARY KEY, для того чтобы делать в основном то же самое что и UNIQUE но с различным окончанием, и наконец ограничение CHECK для определения ваших собственных сделанных на заказ условий, чтобы значения встреченные перед ними могли бы быть введены.
   Кроме того, вы можете использовать предложение DEFAULT, которое будет автоматически вставлять значение по умолчанию в любое поле с именем не указаным в INSERT, так же как вставляется значение NULL когда предложение DEFAULT не установлено и отсутствует ограничение NOT NULL.
   FOREIGN KEY или REFERENCES ограничения о которых вы узнаете в Главе 19 очень похожи на них, за исключением того, что они связывают группу из одного или более полей с другой группой, и таким образом сразу воздействуют на значения которые могут быть введены в любую из этих групп.
### РАБОТА С SQL
   * Создайте таблицу Порядков так чтобы все значения поля onum, а также все комбинации полей cnum и snum отличались друг от друга, и так что бы значения NULL исключались из поля даты.
   * Создайте таблицу Продавцов так чтобы комиссионные, по умолчанию, составляли 10%, не разрешались значения NULL, чтобы поле snum являлось первичным ключом, и чтобы все имена были в алфавитном порядке между A и M включительно( учитывая, что все имена будут напечатаны в верхнем регистре ).
   * Создайте таблицу Порядков, будучи уверенными в том что поле onum больше чем поле cnum, а cnum больше чем snum. Запрещены значения NULL в любом из этих трех полей.




 